package com.flysh.host.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.flysh.host.model.Role;
import com.flysh.host.util.DaoUtil;

public class RoleDaoImpl implements RoleDao{

	
	public List<Role> getRolesByUserId(Long userId) {
		List<Role> list = new ArrayList<Role>();
		Connection conn = DaoUtil.getConnection();
		PreparedStatement ps  = null;
		ResultSet rs = null;
		String sql = "select r.ID as id,r.NAME as name from T_ROLE r where r.ID in (select ROLE_ID from T_USER_ROLE where USER_ID=?)";
		try {
			 ps = conn.prepareStatement(sql);
			 ps.setObject(1, userId);
			 rs = ps.executeQuery();
			 while(rs.next()){
				 Role r = new Role();
				 r.setId(rs.getLong("id"));
				 String name = rs.getString("name");
				 r.setRoleName(name);
				 list.add(r);
			 }
			 
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DaoUtil.close(conn, ps, rs);
		}
		return list;
	}
	
	public List<Role> getAllRoles() {
		List<Role> list = new ArrayList<Role>();
		Connection conn = DaoUtil.getConnection();
		PreparedStatement ps  = null;
		ResultSet rs = null;
		String sql = "select r.ID as id,r.NAME as name from t_role r";
		try {
			 ps = conn.prepareStatement(sql);
			 rs = ps.executeQuery();
			 while(rs.next()){
				 Role r = new Role();
				 r.setId(rs.getLong("id"));
				 r.setRoleName(rs.getString("name"));
				 list.add(r);
			 }
			 
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DaoUtil.close(conn, ps, rs);
		}
		return list;
	}

	
	public int remove(Long roleId) {
		int num=0;
		Connection conn = DaoUtil.getConnection();
		PreparedStatement ps  = null;
		String sql = "delete from T_ROLE where id=?";
		 try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, roleId);
			num = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DaoUtil.close(conn, ps, null);
		}
		return num;
	}


	public int save(Role role) {
		int num=0;
		Connection conn = DaoUtil.getConnection();
		PreparedStatement ps  = null;
		String sql = "insert into T_ROLE(NAME)values(?)";
		 try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, role.getRoleName());
			num = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DaoUtil.close(conn, ps, null);
		}
		return num;
	}

	
	public int update(Role role) {
		int num=0;
		Connection conn = DaoUtil.getConnection();
		PreparedStatement ps  = null;
		String sql = "update T_ROLE set NAME=? where ID=?";
		 try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, role.getRoleName());
			ps.setObject(2, role.getId());
			num = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DaoUtil.close(conn, ps, null);
		}
		return num;
	}


	

}
